package com.dragon.test.hibernate.query.action;

import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.query.NativeQuery;
import org.hibernate.query.Query;
import org.junit.Test;

import com.dragon.test.hibernate.query.bean.Customer;
import com.dragon.test.hibernate.query.bean.LinkMan;
import com.dragon.test.hibernate.util.HibernateUtils;

/**
 * hibernate查询
 * 
 * @author SmallDragon
 *
 */
public class HibernateQuery {

	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

	/**
	 * OID查询 和 对象导航查询
	 */
	@Test
	public void query1() {
		SessionFactory sessionFactory = null;
		Session session = null;
		Transaction transaction = null;
		
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.getCurrentSession();
			transaction = session.beginTransaction();
			
			//get()就是OID查询
			Customer customer = session.get(Customer.class, 3);
			System.out.println(customer);
			
			//对象导航查询，直接使用对象查询
			//并且默认是关联级别的延迟查询，可以在映射中配置,默认是lazy=true
			Set<LinkMan> linkManSet = customer.getLinkMans();
			System.out.println(linkManSet);
			
			//延迟 检索/查询
			Customer customer2 = session.load(Customer.class, 1);
			System.out.println(customer2.getCustId());
			System.out.println(customer2.getCustName());
			
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		} finally {
			sessionFactory.close();
		}
	}
	
	/**
	 * HQL查询
	 */
	@Test
	public void hqlQuery() {
		SessionFactory sessionFactory = null;
		Session session = null;
		Transaction transaction = null;
		
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.getCurrentSession();
			transaction = session.beginTransaction();
			
			/* 查询所有 */
//			Query<Customer> query = session.createQuery("from Customer",Customer.class);
//			List<Customer> customerList = query.list();
//			System.out.println(customerList);
			
			/* 条件查询 */
//			Query<Customer> query = session.createQuery("from Customer where custSource=? and custLevel=?",Customer.class);
//			query.setParameter(0, "网络");
//			query.setParameter(1, "vip");
//			List<Customer> customerList = query.list();
//			System.out.println(customerList);
			
			/* 模糊查询 */
//			Query<Customer> query = session.createQuery("from Customer where custName like ?",Customer.class);
//			query.setParameter(0, "%里%");
//			List<Customer> customerList = query.list();
//			System.out.println(customerList);
			
			/* 排序查询 */
//			Query query = session.createQuery("from Customer order by custId desc");
//			List<Customer> customerList = query.list();
//			System.out.println(customerList);
			
			/* 分页查询 */
//			Query query = session.createQuery("from Customer");
//			query.setFirstResult(0);
//			query.setMaxResults(2);
//			List<Customer> customerList = query.list();
//			System.out.println(customerList);
			
			/* 投影查询 */
//			Query query = session.createQuery("select custId,custName from Customer");
//			List<Object[]> customerList = query.list();
//			for (Object[] o : customerList) {
//				for (int i = 0; i < o.length; ++i) {
//					System.out.print(o[i]+",");
//				}
//				System.out.println();
//			}
			
			/* 聚合查询 */
			Query query = session.createQuery("select count(*) from Customer");
			Object count = query.uniqueResult();
			System.out.println(count);
			
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		} finally {
			sessionFactory.close();
		}
	}
	
	/**
	 * HQL多表查询
	 */
	@Test
	public void hqlMultipleTableQuery() {
		SessionFactory sessionFactory = null;
		Session session = null;
		Transaction transaction = null;
		
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.getCurrentSession();
			transaction = session.beginTransaction();
			
			/* 内连接，结果list中的元素是数组 */
//			Query query = session.createQuery("from Customer c inner join c.linkMans");
//			List customerList = query.list();
//			System.out.println(customerList);
			
			/* 迫切内连接,结果list中的元素是对象 */
//			Query query = session.createQuery("from Customer c inner join fetch c.linkMans");
//			List customerList = query.list();
//			System.out.println(customerList);
			
			/* 左外连接 */
//			Query query = session.createQuery("from Customer c left outer join c.linkMans");
//			List customerList = query.list();
//			System.out.println(customerList);
			
			/* 迫切左外连接 */
//			Query query = session.createQuery("from Customer c left outer join fetch c.linkMans");
//			List customerList = query.list();
//			System.out.println(customerList);
			
			/* 右外连接 */
			Query query = session.createQuery("from Customer c right outer join c.linkMans");
			List customerList = query.list();
			System.out.println(customerList);
			
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		} finally {
			sessionFactory.close();
		}
	}
	
	/**
	 * QBC查询,Query By Criteria，不需要写语句
	 */
	@Test
	public void qbcQuery() {
		SessionFactory sessionFactory = null;
		Session session = null;
		Transaction transaction = null;
		
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.getCurrentSession();
			transaction = session.beginTransaction();
			
			/* 查询所有 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			List<Customer> customers = criteria.list();
//			System.out.println(customers);
			
			/* 条件查询 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			criteria.add(Restrictions.eq("custName", "阿里"));
//			List<Customer> customers = criteria.list();
//			System.out.println(customers);
			
			/* 模糊查询 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			criteria.add(Restrictions.like("custName", "%歌%"));
//			List<Customer> customers = criteria.list();
//			System.out.println(customers);
			
			/* 排序查询 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			criteria.addOrder(Order.desc("custId"));
//			List<Customer> customers = criteria.list();
//			System.out.println(customers);
			
			/* 分页查询 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			criteria.setFirstResult(2);
//			criteria.setMaxResults(2);
//			List<Customer> customers = criteria.list();
//			System.out.println(customers);
			
			/* 统计查询 */
//			Criteria criteria = session.createCriteria(Customer.class);
//			criteria.setProjection(Projections.rowCount());
//			Object o = criteria.uniqueResult();
//			System.out.println(o);
			
			/* 离线查询 */
			DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class);
			detachedCriteria.add(Restrictions.eq("custName", "阿里"));
			Criteria criteria = detachedCriteria.getExecutableCriteria(session);
			List<Customer> customers = criteria.list();
			System.out.println(customers);
			
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		} finally {
			sessionFactory.close();
		}
	}
	
	/**
	 * 本地sql查询
	 */
	@Test
	public void sqlQuery() {
		SessionFactory sessionFactory = null;
		Session session = null;
		Transaction transaction = null;
		
		try {
			sessionFactory = HibernateUtils.getSessionFactory();
			session = sessionFactory.getCurrentSession();
			transaction = session.beginTransaction();
			
			NativeQuery nativeQuery = session.createNativeQuery("select * from t_customer where cust_name = '百度'",
					Customer.class);
			List<Customer> customers = nativeQuery.list();
			System.out.println(customers);
			
			
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
		} finally {
			sessionFactory.close();
		}
	}
	

}
